{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "NOTE:\n",
    "-----\n",
    "\n",
    "Please run the below cells first before proceeding- you'll need them soon!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: None@None'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Movies;\n",
    "CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1967, 'John Joe', 123);\n",
    "INSERT INTO Movies VALUES('The Databaser', 1992, 'John Bob', 190);\n",
    "INSERT INTO Movies VALUES('Database Wars', 1998, 'John Jim', 176);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    }
   ],
   "source": [
    "%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;\n",
    "%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);\n",
    "for i in range(1,6):\n",
    "    %sql INSERT INTO A VALUES (:i, :i+1)\n",
    "for i in range(1,11,3):\n",
    "    %sql INSERT INTO B VALUES (:i, :i+2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 3-1:\n",
    "------------\n",
    "\n",
    "ORDER BY semantics, set operators & nested queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>year</th>\n",
       "        <th>director</th>\n",
       "        <th>length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Database Wars</td>\n",
       "        <td>1967</td>\n",
       "        <td>John Joe</td>\n",
       "        <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Databaser</td>\n",
       "        <td>1992</td>\n",
       "        <td>John Bob</td>\n",
       "        <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Database Wars</td>\n",
       "        <td>1998</td>\n",
       "        <td>John Jim</td>\n",
       "        <td>176</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Database Wars', 1967, u'John Joe', 123),\n",
       " (u'The Databaser', 1992, u'John Bob', 190),\n",
       " (u'Database Wars', 1998, u'John Jim', 176)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM movies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #1\n",
    "-----------\n",
    "\n",
    "**Can you write the movie query from lecture as a single SFW query?**\n",
    "\n",
    "Recall that we are trying to find **all movie titles that were used for more than one movie.** You may assume that no two movies in the same year have the same title. Our schema for the `movies` table is:\n",
    "\n",
    "> * title STRING\n",
    "> * year INT\n",
    "> * director STRING\n",
    "> * length INT\n",
    "\n",
    "Let's try to write the nested query that solves this from lecture:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(sqlite3.OperationalError) near \"SELECT\": syntax error [SQL: u'SELECT m.title \\nFROM Movies m\\nWHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);']\n"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT m.title \n",
    "FROM Movies m\n",
    "WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What?  This doesn't work?  Why?\n",
    "\n",
    "**ANY doesn't exist in SQLite!**  Can we do this query without nesting?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exercise #2\n",
    "--------------------\n",
    "\n",
    "Consider the two relations $A$ and $B$ below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>x</th>\n",
       "        <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 2), (2, 3), (3, 4), (4, 5), (5, 6)]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM A;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>x</th>\n",
       "        <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>7</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 3), (4, 6), (7, 9), (10, 12)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM B;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assuming no duplicates, can you write an `INTERSECT` query, **just over the $x$ attribute**, without using `INTERSECT` OR nested queries?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is this operation called?\n",
    "\n",
    "Next, using set operators again as well, can you return all the _full_ tuples in $A$ and $B$ that overlap in $x$ attributes?  Write your query here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
